--    Author    : ChenErHao
--    Name      : DM.T_INTERTEMPORAL_CHECK.HQL
--    Functions : 存贷款跨期校验
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-25  ChenErHao       1.CREATE THE PROCEDURE
--

with tmp_loab as(
select
     o.legal_org_id,
     o.legal_org_name,
     sum(case when data_date = '#V_DATA_DATE#'        then nvl(t.loan_balance,0) else 0 end) as CUR_BALANCE,
     sum(case when data_date = '#V_LAST_1M_END_DATE#' then nvl(t.loan_balance,0) else 0 end) as PRE_BLANCE
from ods.loab t
left join dmcode.t_org_manage o on t.data_org_code = o.data_org_id
where data_date in ('#V_DATA_DATE#','#V_LAST_1M_END_DATE#')
group by o.legal_org_id,
         o.legal_org_name
),
tmp_loaf as(
select
     o.legal_org_id,
     o.legal_org_name,
     sum(case when t.loan_grant_recover = 1 then nvl(t.loan_occurrence,0) else 0 end) as CUR_ISSUE,
     sum(case when t.loan_grant_recover = 0 then nvl(t.loan_occurrence,0) else 0 end) as CUR_BACK
from ods.loaf t
left join dmcode.t_org_manage o on t.data_org_code = o.data_org_id
where  data_date = '#V_DATA_DATE#'
group by o.legal_org_id,
         o.legal_org_name
)
INSERT OVERWRITE TABLE dm.t_intertemporal_check PARTITION(P_MONTH = '#V_DATA_MONTH#') 
SELECT 
     o.legal_org_id,
     o.legal_org_name,
     round(nvl(a.PRE_BLANCE,0),4),   --上期贷款余额
     round(nvl(b.CUR_ISSUE,0),4),    --当期贷款发放额
     round(nvl(b.CUR_BACK,0),4),     --当期贷款收回额
     round(nvl(a.CUR_BALANCE,0),4),  --当期贷款余额
     ROUND((NVL(a.PRE_BLANCE,0) + NVL(b.CUR_ISSUE,0) - NVL(b.CUR_BACK,0) - NVL(a.CUR_BALANCE,0))*100/a.CUR_BALANCE,2) AS T_RANGE,
     '#V_DATA_DATE#' AS DATA_DATE,
     CASE WHEN ABS((NVL(a.PRE_BLANCE,0) + NVL(b.CUR_ISSUE,0) - NVL(b.CUR_BACK,0) - NVL(a.CUR_BALANCE,0))*100/a.CUR_BALANCE) <= c.check_value then 1 
     else 0 end AS CHECK_STATUS  -- （上期贷款余额+当期贷款发放额-当期贷款收回额-当期贷款余额）*100/当期贷款余额”的绝对值小于等于该数值，跨期校验通过，否则校验不通过
FROM (select distinct legal_org_id,legal_org_name from dmcode.t_org_manage) o
left join tmp_loab a on o.legal_org_id = a.legal_org_id
left join tmp_loaf b on o.legal_org_id = b.legal_org_id
left join dmcode.T_CHECK_VALUE c
;